import mysql_helper
import py_time

VERSION = 1002
TABLE = 'st_punch_2018'
OUT_TABLE = 'st_punch_2018_out'

# 如果真的想要在函数体内修改全局变量的值，就要使用global关键字
db = mysql_helper.db_connect()
cursor = db.cursor()
s_time = py_time.get_time('06:00:00')
m_time = py_time.get_time('12:00:00')
base_time = '18:00:00'
l_time = py_time.get_time(base_time)


def get_staffs():
    cursor.execute('SELECT label from {table} GROUP BY label'.format(table=TABLE))
    results = cursor.fetchall()
    columns = []
    for row in results:
        columns.append(row[0])
    return columns


def get_date_list(staff):
    cursor.execute("SELECT date_ from {table} where label='{name}' GROUP BY date_"
                   .format(table=TABLE, name=staff))
    results = cursor.fetchall()
    columns = []
    for row in results:
        columns.append(row[0])
    return columns


def get_times(staff, date):
    cursor.execute("SELECT time_ from {table} where label='{name}' AND date_='{date}'"
                   .format(table=TABLE, name=staff, date=date))
    results = cursor.fetchall()
    columns = []
    for row in results:
        columns.append(str(row[0]))
    return columns


def analyze_times(times):
    global begin_time
    global end_time
    if len(times) == 1:
        time_ = py_time.get_time(times[0])
        # 早上没打卡
        if time_ > l_time:
            begin_time = '09:00:00'
            end_time = str(time_)
        # 晚上没打卡
        elif s_time < time_ < m_time:
            begin_time = str(time_)
            end_time = '18:00:00'
    else:
        begin_time = py_time.get_time(times[0])
        # 早上没打卡
        if begin_time > l_time:
            begin_time = '09:00:00'
        end_time = py_time.get_time(times[-1])
        # 晚上没打卡
        if s_time < end_time < m_time:
            end_time = '18:00:00'
    return begin_time, end_time


def do_process():
    staffs = get_staffs()
    sql = "insert into {table} (label,date_,beginTime,endTime) values ('{label}','{date}','{begin}','{end}')"
    for staff in staffs:
        print('当前员工： {}'.format(staff))
        date_list = get_date_list(staff)
        for date_ in date_list:
            times = get_times(staff, date_)
            begin_, end_ = analyze_times(times)
            cursor.execute(sql.format(table=OUT_TABLE,
                                      label=staff, date=date_,
                                      begin=begin_, end=end_))
        db.commit()


def do_offset():
    print('正在计算偏移')
    cursor.execute("select id,endTime from {table} where endTime > '18:00:00'"
                   .format(table=OUT_TABLE))
    results = cursor.fetchall()
    for row in results:
        id_ = row[0]
        time_ = str(row[1])
        offset = py_time.get_offset(time_, base_time)
        sql = "update {table} set endOffset={offset} where id={id}"
        cursor.execute(sql.format(table=OUT_TABLE, offset=offset, id=id_))
    db.commit()
    print('计算完成')


# print('你好，世界！')
# print(analyze_times(['08:09:10', '16:00:00']))
do_process()
do_offset()
